package model

import "database/sql"
import (
	_ "github.com/go-sql-driver/mysql"
	"log"
	"fmt"
	"baliance.com/gooxml/document"
	"baliance.com/gooxml/schema/soo/wml"
	"baliance.com/gooxml/color"
	"baliance.com/gooxml/measurement"
)

type TableInfo struct {
	ColumnName    string `json:"columnName"`
	ColumnType    string `json:"columnType"`
	MaxLength     string `json:"maxLength"`
	ColumnComment string `json:"columnComment"`
	NullAble      string `json:"nullAble"`
}

var db *sql.DB

const DOC_NAME = "数据库设计文档.docx"

type TableInfos []TableInfo

func QueryAllTable() (err error) {
	rows, err := db.Query("select table_name tableName from information_schema.tables where table_schema='fairy'")
	defer rows.Close()
	if err != nil {
		return err
	}

	doc := document.New()
	for rows.Next() {
		var tableName string
		rows.Scan(&tableName)
		tableInfos, err := queryTableInfo(tableName)
		if err != nil {
			fmt.Print("query tableInfo error,", err)
			continue
		}
		writeTable(doc, tableInfos, tableName)
	}
	doc.SaveToFile(DOC_NAME)
	return err
}

func queryTableInfo(tableName string) (TableInfos, error) {
	rows, err := db.Query("SELECT t.column_name ColumnName,t.data_type ColumnType,t.character_maximum_length MaxLength,t.column_comment columnComment,t.IS_NULLABLE NullAble FROM information_schema.COLUMNS t WHERE TABLE_SCHEMA =? AND TABLE_NAME = ?", "fairy", tableName)
	defer rows.Close()
	if err != nil {
		return nil, err
	}
	var (
		ColumnName    string
		ColumnType    string
		MaxLength     string
		ColumnComment string
		NullAble      string
	)
	var tableInfos []TableInfo = nil
	for rows.Next() {
		rows.Scan(&ColumnName, &ColumnType, &MaxLength, &ColumnComment, &NullAble)
		tableInfos = append(tableInfos, TableInfo{ColumnName: ColumnName, MaxLength: MaxLength, ColumnType: ColumnType, ColumnComment: ColumnComment, NullAble: NullAble})
	}

	return tableInfos, nil

}

func init() {
	var err error
	db, err = sql.Open("mysql", "root:root@/fairy")
	if err != nil {
		log.Fatal("connect database error:", err)
	}
}

func writeTable(doc *document.Document, tableInfos TableInfos, tableName string) {
	para := doc.AddParagraph()
	run := para.AddRun()
	para.SetStyle("Heading1")
	run.AddText("表" + tableName + "的栏信息")

	table := doc.AddTable()
	// width of the page
	table.Properties().SetWidthPercent(100)
	// with thick borers
	borders := table.Properties().Borders()
	borders.SetAll(wml.ST_BorderSingle, color.Auto, measurement.Point)
	row := writHeader(&table)

	veryLightGray := color.RGB(240, 240, 240)
	for index, tableInfo := range tableInfos {
		row = table.AddRow()
		if index%2 == 0 {
			columnNameCall := row.AddCell()
			columnNameCall.Properties().SetShading(wml.ST_ShdSolid, veryLightGray, color.Auto)
			columnNameCall.AddParagraph().AddRun().AddText(tableInfo.ColumnName)

			columnTypeCall := row.AddCell()
			columnTypeCall.Properties().SetShading(wml.ST_ShdSolid, veryLightGray, color.Auto)
			columnTypeCall.AddParagraph().AddRun().AddText(tableInfo.ColumnType)

			maxLengthCall := row.AddCell()
			maxLengthCall.Properties().SetShading(wml.ST_ShdSolid, veryLightGray, color.Auto)
			maxLengthCall.AddParagraph().AddRun().AddText(tableInfo.MaxLength)

			nullAbleCall := row.AddCell()
			nullAbleCall.Properties().SetShading(wml.ST_ShdSolid, veryLightGray, color.Auto)
			nullAbleCall.AddParagraph().AddRun().AddText(tableInfo.NullAble)

			columnCommentCall := row.AddCell()
			columnCommentCall.Properties().SetShading(wml.ST_ShdSolid, veryLightGray, color.Auto)
			columnCommentCall.AddParagraph().AddRun().AddText(tableInfo.ColumnComment)

			continue
		}
		row.AddCell().AddParagraph().AddRun().AddText(tableInfo.ColumnName)
		row.AddCell().AddParagraph().AddRun().AddText(tableInfo.ColumnType)
		row.AddCell().AddParagraph().AddRun().AddText(tableInfo.MaxLength)
		row.AddCell().AddParagraph().AddRun().AddText(tableInfo.NullAble)
		row.AddCell().AddParagraph().AddRun().AddText(tableInfo.ColumnComment)
	}
}

func writHeader(table *document.Table) document.Row {
	row := table.AddRow()
	veryLightGray := color.RGB(240, 240, 240)
	for _, value := range []string{"属性名称", "类型", "最大长度", "允许为空", "备注"} {
		columnNameCall := row.AddCell()
		columnNameCall.Properties().SetShading(wml.ST_ShdSolid, veryLightGray, color.Auto)
		columnNameCall.AddParagraph().AddRun().AddText(value)
	}
	return row
}

func closeDbConn() {
	db.Close()
}
